package com.coamc.xlsunit;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import javax.sql.DataSource;

import org.beetl.sql.core.ConnectionSource;
import org.beetl.sql.core.ConnectionSourceHelper;
import org.beetl.sql.core.DefaultNameConversion;
import org.beetl.sql.core.OnConnection;
import org.beetl.sql.core.SQLManager;
import org.beetl.sql.core.SQLManagerBuilder;
import org.beetl.sql.core.SQLReady;
import org.beetl.sql.core.db.ColDesc;
import org.beetl.sql.core.db.DBStyle;
import org.beetl.sql.core.db.TableDesc;
import org.beetl.sql.core.kit.CaseInsensitiveOrderSet;

/**
 * 直接使用BeetlSql来完成数据库访问
 * @author xiandafu
 *
 */
public class BeetlSqlDBAccess implements DBAccess {

	DataSource ds = null;
	SQLManager sqlManager = null;

	public BeetlSqlDBAccess(DataSource ds, DBStyle dbStyle) {
		this.ds = ds;
		ConnectionSource source = ConnectionSourceHelper.getSingle(ds);
		SQLManagerBuilder smBuilder = SQLManager.newBuilder(source);
		// 采用默认命名规则，列名就是map里的key的名字
		SQLManager sm = smBuilder.setDbStyle(dbStyle).setNc(new DefaultNameConversion()).build();
	}

	public BeetlSqlDBAccess(SQLManager sqlManager) {
		this.sqlManager = sqlManager;
	}

	@Override
	public Map<String, Object> save(String tableName, Map<String, Object> row, List<String> id) {
		final TableDesc tableDesc = sqlManager.getMetaDataManager().getTable(tableName);
		StringBuilder sb = new StringBuilder("insert into ");
		sb.append(tableName);
		sb.append(" ( ");
		final Set<String> autos = new CaseInsensitiveOrderSet<String>();

		for (String col : row.keySet()) {
			if (tableDesc.containCol(col)) {
				if (tableDesc.getIdNames().contains(col)) {
					// 主键，需要重新取回来
					if (isAutoInc(tableDesc, col) && !isNumber(row.get(col))) {
						autos.add(col);
						continue;
					}

				}
				sb.append(col).append(",");
			}

		}
		// 删除最后一个逗号
		sb.setLength(sb.length() - 1);
		sb.append(") values (");
		List<Object> values = new ArrayList<Object>();
		final List<String> allCols = new ArrayList<String>();
		for (String col : row.keySet()) {
			if (tableDesc.containCol(col)) {
				if (tableDesc.getIdNames().contains(col)) {
					if (isAutoInc(tableDesc, col) && !isNumber(row.get(col))) {
						continue;
					}

				}
				sb.append("?,");
				values.add(row.get(col));
				allCols.add(col);
			}

		}
		sb.setLength(sb.length() - 1);
		sb.append(")");
		if (autos.size() > 1) {
			throw new RuntimeException("最多只支持一个自增主键");
		}
		final String sql = sb.toString();
		final Object[] paras = values.toArray();
		Object incColValue = sqlManager.executeOnConnection(new OnConnection<Object>() {
			@Override
			public Object call(Connection conn) throws SQLException {
				PreparedStatement ps = null;
				if (autos.isEmpty()) {
					ps = conn.prepareStatement(sql);
				} else {
					// 实际上只会有一个跟主键相关的自增主键
					String incColName = (String) autos.toArray()[0];
					ps = conn.prepareStatement(sql, new String[]{incColName});
				}
				int dbType = sqlManager.getDbStyle().getDBType();
				int i = 1;
				for (Object obj : paras) {
					String colName = allCols.get(i - 1);
					ColDesc desc = tableDesc.getColDesc(colName);
					if (obj == null) {
						ps.setNull(i++, desc.sqlType);
						continue;
					}
					if (dbType == DBStyle.DB_ORACLE || dbType == DBStyle.DB_POSTGRES || dbType == DBStyle.DB_DB2
							|| dbType == DBStyle.DB_SQLSERVER) {
						if (obj instanceof java.util.Date) {
							obj = new Timestamp(((java.util.Date) obj).getTime());
						}
					}
					try {
						ps.setObject(i++, obj, desc.sqlType);
					} catch (RuntimeException ex) {
						throw new RuntimeException("保存出错，在 " + i + "位置，value=" + obj, ex);
					} catch (SQLException ex) {
						throw new RuntimeException("保存出错，在 " + i + "位置，value=" + obj, ex);
					}


				}
				ps.executeUpdate();
				if (!autos.isEmpty()) {
					ResultSet seqRs = ps.getGeneratedKeys();
					seqRs.next();
					Object key = seqRs.getObject(1);
					seqRs.close();
					return key;
				} else {
					return null;
				}
			}

		});
		// 主键的值
		Map<String, Object> ret = new HashMap<String, Object>();
		for (String key : tableDesc.getIdNames()) {
			if (autos.contains(key)) {
				ret.put(key, incColValue);
			} else {
				ret.put(key, row.get(key));
			}
		}

		System.out.println("准备数据 " + tableName + " values " + row);
		return ret;
	}

	@Override
	public Map<String, Object> findById(String tableName, Map<String, Object> id) {
		StringBuilder sb = new StringBuilder("select * from ");
		sb.append(tableName).append(" where ");
		List<Object> values = new ArrayList<Object>();
		for (String key : id.keySet()) {
			sb.append(key).append("=").append("? ").append(" and ");
			values.add(id.get(key));
		}
		// 删除最后给and
		sb.setLength(sb.length() - 4);
		List<Map> list = sqlManager.execute(new SQLReady(sb.toString(), values.toArray()), Map.class);
		if (list.size() == 0) {
			return null;
		} else {
			return toExcelData(list.get(0));
		}
	}

	@Override
	public List<Map<String, Object>> query(String tableName, String where, VariableTable vars) {
		StringBuilder sb = new StringBuilder("select * from ");
		sb.append(tableName).append(" where ").append(where);
		Map paras = new HashMap();
		paras.put("vars", vars);
		// 参考BeetlSQLVarRef 如何获取excel变量
		List<Map> list = sqlManager.execute(sb.toString(), Map.class, paras);
		// 不能强制转化到 return List<Map<String, Object>>
		List<Map<String, Object>> newList = new ArrayList<Map<String, Object>>();
		for (Map map : list) {
			Map<String, Object> newMap = map;
			newList.add(newMap);
		}

		return toExcelData(newList);
	}


	private boolean isAutoInc(TableDesc desc, String id) {
		ColDesc col = desc.getColDesc(id);
		return col.isAuto;
	}

	private boolean isNumber(Object o) {
		if (o == null) {
			return false;
		}

		if (o instanceof Number) {
			return true;
		} else {
			return false;
		}
	}

	private Map toExcelData(Map<String, Object> old) {
		Map map = new CaseInsensitiveHashMap();
		for (Entry<String, Object> entry : old.entrySet()) {
			String key = entry.getKey();
			String col = this.sqlManager.getNc().getColName(key);
			map.put(col, entry.getValue());
		}
		return map;
	}

	private List toExcelData(List<Map<String, Object>> list) {
		List newList = new ArrayList(list.size());
		for (Map<String, Object> map : list) {
			newList.add(toExcelData(map));
		}
		return newList;
	}

}
